package com.efesco.service.compensation;

import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.bstek.dorado.common.DoradoContext;
import com.bstek.dorado.data.db.SqlDataset;
import com.efesco.common.CommUtil;
import com.efesco.common.Tools;
import com.efesco.system.User;

public class SQLClass {
	DoradoContext context = DoradoContext.getContext();

	User user = (User) context.getAttribute(context.SESSION, "User");

	public String M_apply_form_sql_dataset_auditing(SqlDataset ds) {
/*		String sql = "SELECT distinct af.APPLY_FORM_ID,af.FORM_CODE,"
				+ "af.APPLY_DATE,e.EMPLOYEE_CODE,e.EMPLOYEE_NAME,"
				+ "sc.COMPANY_CODE,sc.COMPANY_CHINESE_NAME,"
				+ "e.CONTACT_TEL1,e.ID_CARD_NUM,e.CONTACT_ADDRESS,"
				+ "e.CONTACT_POSTALCODE,e.EMPLOYEE_ID,sc.FOREIGN_ENTERPRISE_ID "
				+ "FROM S_APPLY_FORM af ,S_EMPLOYEE e,S_single_company sc,"
				+ "s_medical_bill mb,s_insure_type it,[table] "
				+ "WHERE (af.EMPLOYEE_ID = e.EMPLOYEE_ID(+)) AND "
				+ "(af.SINGLE_COMPANY_ID = sc.SINGLE_COMPANY_ID(+)) AND "
				+ "(af.APPLY_FORM_ID = mb.APPLY_FORM_ID(+)) AND "
				+ "(mb.insure_type_ID = it.insure_type_ID(+)) AND [field] and"
				+ "((af.IF_MATERIAL_ALL = '1') and (mb.if_sended = '0')) ";*/
		String sql = "SELECT  af.APPLY_FORM_ID,af.FORM_CODE,"
			+ "af.APPLY_DATE,e.EMPLOYEE_CODE,e.EMPLOYEE_NAME,"
			+ "sc.COMPANY_CODE,sc.COMPANY_CHINESE_NAME,"
			+ "e.CONTACT_TEL1,e.ID_CARD_NUM,e.CONTACT_ADDRESS,"
			+ "e.CONTACT_POSTALCODE,e.EMPLOYEE_ID,sc.FOREIGN_ENTERPRISE_ID,mb.*,vr.AMOUNT as vramount,vr.REMARK,vr.DISEASE_REASON,it.PRODUCT_NAME "
			+ "FROM S_APPLY_FORM af ,S_EMPLOYEE e,S_single_company sc,"
			+ "s_medical_bill mb,s_insure_type it,s_verify_result vr,[table] "
			+ "WHERE af.EMPLOYEE_ID = e.EMPLOYEE_ID AND "
			+ "af.SINGLE_COMPANY_ID = sc.SINGLE_COMPANY_ID AND "
			+ "af.APPLY_FORM_ID = mb.APPLY_FORM_ID AND "
			+ "vr.medical_bill_id(+)= mb.medical_bill_id AND "
			+ "mb.insure_type_ID = it.insure_type_ID AND [field] and "
			+ "af.IF_MATERIAL_ALL = '1' and mb.if_sended = '0' ";
		sql = CommUtil.getRoleSql(sql, "sc");
		sql += CommUtil.setConditionSQL(ds)+" order by af.APPLY_FORM_ID";
		return sql;
	}

	public String M_apply_form_dataset_Apply_Form(SqlDataset ds) {
		String sql = "SELECT "
				+ "distinct af.APPLY_FORM_ID,sc.COMPANY_CODE,"
				+ "sc.COMPANY_SHORT_NAME,af.FORM_CODE,"
				+ "af.APPLY_DATE,e.EMPLOYEE_CODE,e.EMPLOYEE_NAME,"
				+ "sc.COMPANY_CHINESE_NAME,af.REMARK,af.IF_MATERIAL_ALL,"
				+ "af.LACK_MATERIAL,af.SINGLE_COMPANY_ID,af.EMPLOYEE_ID "
				+ "FROM s_apply_form af,s_single_company sc,s_EMPLOYEE e,s_medical_bill mb,[table] "
				+ "WHERE af.SINGLE_COMPANY_ID = sc.SINGLE_COMPANY_ID AND "
				+ "af.EMPLOYEE_ID = e.EMPLOYEE_ID and af.apply_form_id=mb.apply_form_id and mb.if_sended=0 and [field] ";
		sql = CommUtil.getRoleSql(sql, "sc");
		sql += CommUtil.setConditionSQL(ds);

		sql += " ORDER BY af.APPLY_FORM_ID";
		return sql;
	}

	public String M_apply_form_dropdown_employee_auto_fill(SqlDataset ds) {
		String sql = "SELECT s_EMPLOYEE.EMPLOYEE_ID,"
				+ "s_EMPLOYEE.SINGLE_COMPANY_ID,"
				+ "s_EMPLOYEE.EMPLOYEE_CODE,"
				+ "s_EMPLOYEE.EMPLOYEE_NAME,s_EMPLOYEE.ID_CARD_NUM,"
				+ "s_EMPLOYEE.P_COMPUTER_CODE,s_EMPLOYEE.employee_status,"
				+ "s_single_company.COMPANY_CODE,s_single_company.COMPANY_CHINESE_NAME"
				+ " FROM s_EMPLOYEE,s_single_company s_single_company,"
				+ "s_insurance_employee s_insurance_employee,[table] "
				+ " WHERE (s_EMPLOYEE.SINGLE_COMPANY_ID = s_single_company.SINGLE_COMPANY_ID(+)) AND "
				+ "(s_EMPLOYEE.EMPLOYEE_ID = s_insurance_employee.EMPLOYEE_ID(+)) and [field]";

		sql = CommUtil.getRoleSql(sql, "S_single_company");
		sql += CommUtil.setConditionSQL(ds);

		return sql;
	}

	public String M_apply_form_dropdown_employee(SqlDataset ds) {
		String sql = "SELECT s_EMPLOYEE.EMPLOYEE_ID,s_EMPLOYEE.EMPLOYEE_NAME,"
				+ "s_EMPLOYEE.EMPLOYEE_CODE,s_SINGLE_COMPANY.COMPANY_CHINESE_NAME,"
				+ "s_SINGLE_COMPANY.COMPANY_CODE,s_SINGLE_COMPANY.SINGLE_COMPANY_ID"
				+ " FROM s_EMPLOYEE,s_SINGLE_COMPANY s_SINGLE_COMPANY,[table] "
				+ " WHERE (s_EMPLOYEE.SINGLE_COMPANY_ID = s_SINGLE_COMPANY.SINGLE_COMPANY_ID(+)) "
				+ "and [field]";
		sql = CommUtil.getRoleSql(sql, "s_SINGLE_COMPANY");
		sql += CommUtil.setConditionSQL(ds);
		return sql;
	}
/*
	public String M_apply_form_create_datum_list1(SqlDataset ds) {
		String sql = "SELECT af.APPLY_FORM_ID,"
				+ "sc.COMPANY_CHINESE_NAME,"
				+ "sc.COMPANY_CODE,e.EMPLOYEE_CODE,"
				+ "e.EMPLOYEE_NAME,af.APPLY_DATE,"
				+ "e.ID_CARD_NUM,mb.BILL_CODE,"
				+ "mb.BILL_DATE,mb.BILL_COUNT,"
				+ "mb.AMOUNT,af.VERIFY_DATE,"
				+ "mb.INSURE_TYPE_ID"
				+ " FROM s_apply_form af,s_employee e,"
				+ "s_medical_bill mb,s_single_company sc,[table]"
				+ " WHERE af.EMPLOYEE_ID = e.EMPLOYEE_ID AND "
				+ "af.APPLY_FORM_ID = mb.APPLY_FORM_ID AND "
				+ "af.SINGLE_COMPANY_ID = sc.SINGLE_COMPANY_ID AND "
				+ "af.IF_MATERIAL_ALL ='1' and mb.if_sended ='0' and [field] ";
		sql = CommUtil.getRoleSql(sql, "sc");
		sql += CommUtil.setConditionSQL(ds);
		return sql;
	}*/
	public String M_apply_form_create_datum_list(SqlDataset ds) {
		String sql = "SELECT af.APPLY_FORM_ID,"
				+ "sc.COMPANY_CHINESE_NAME,"
				+ "sc.COMPANY_CODE,e.EMPLOYEE_CODE,"
				+ "e.EMPLOYEE_NAME,af.APPLY_DATE,"
				+ "e.ID_CARD_NUM,mb.BILL_CODE,"
				+ "mb.BILL_DATE,mb.BILL_COUNT,"
				+ "mb.AMOUNT,af.VERIFY_DATE,mb.if_sended,"
				+ "mb.INSURE_TYPE_ID,vr.amount as vamount,eb.bank_account,vr.verify_type"
				+ " FROM s_apply_form af,s_employee e,"
				+ "s_medical_bill mb,s_single_company sc,s_verify_result vr,s_employee_bankcard eb,[table]"
				+ " WHERE af.EMPLOYEE_ID = e.EMPLOYEE_ID AND vr.medical_bill_id=mb.medical_bill_id and "
				+ "af.APPLY_FORM_ID = mb.APPLY_FORM_ID AND eb.employee_id=e.employee_id and "
				+ "af.SINGLE_COMPANY_ID = sc.SINGLE_COMPANY_ID AND "
				+ "af.IF_MATERIAL_ALL ='1' and [field] ";
		sql = CommUtil.getRoleSql(sql, "sc");
		sql += CommUtil.setConditionSQL(ds);
		return sql;
	}

	public String M_apply_form_dataset_send_page(SqlDataset ds)
			throws Exception {
		String sql = "select eb.bank_name,sc.company_short_name,eb.bank_account,"
				+ "sum(sl.amount)as amount,e.employee_id,e.employee_name,e.employee_code,"
				+ "sc.company_code,sc.single_company_id "
				+ "from s_send_list sl ,s_employee e ,s_single_company sc,s_employee_bankcard eb,s_employee_bankcard_busi ebb"
				+ " where sl.employee_id=e.employee_id and sl.single_company_id=sc.single_company_id"
				+ " and e.employee_id=eb.employee_id and eb.employee_bank_card_id=ebb.employee_bank_card_id and sl.send_date is not null and ebb.busi_type='2'";
		sql += CommUtil.setConditionSQL(ds);
/*		
		int i = Tools.toInt(ds.parameters().getString("send_object"));
		if (i != 0) {
			switch (i) {
			case 1:
				sql += "and d.card_flag in (2,3)";
				break;
			case 2:
				break;
			case 3:
				sql += "and (d.card_flag=4  and (d.card_flag in (2,3)))";
				break;
			default:
			}
		}*/

		sql += " group by sc.company_short_name,eb.bank_account,eb.bank_name,"
				+ "e.employee_id,e.employee_name,e.employee_code,sc.company_code,sc.single_company_id";
		// sql=CommUtil.getRoleSql(sql,"s_single_company");

		return sql;
	}

	public String M_apply_form_dataset_send_out_list_c(SqlDataset ds) {
		String sql = "SELECT sl.GENERATE_DATE,u.real_name,"
				+ "COUNT(sl.GENERATE_DATE) AS GENERATE_DATE1 "
				+ "FROM s_send_list sl,s_user u,s_verify_result vr,s_medical_bill mb "
				+ "where sl.fesc_employee_id=u.user_id and" +
						" sl.send_list_id=vr.sendlist_id and " +
						"vr.medical_bill_id=mb.medical_bill_id ";
		sql += CommUtil.setConditionSQL(ds);
		sql += " GROUP BY GENERATE_DATE,real_name ORDER BY sl.GENERATE_DATE DESC";
		// sql=CommUtil.getRoleSql(sql,"s_single_company");

		return sql;
	}

	public String M_apply_form_dataset_insure_type(SqlDataset ds) {
		String sql = "SELECT it.INSURE_TYPE_ID,"
				+ "it.FOREIGN_ENTERPRISE_ID,it.PRODUCT_ID,"
				+ "it.SINGLE_COMPANY_ID,it.AUDITING_MODE,"
				+ "it.REMARK,it.PRICE,p.PRODUCT_NAME PRODUCT_NAME_I,"
				+ "sc.COMPANY_CHINESE_NAME,fe.FOREIGN_ENTERPRISE_NAME,"
				+ "it.INSURER_ID,it.PRODUCT_NAME ,i.INSURER_NAME "
				+ "FROM s_insure_type it,s_foreign_enterprise fe,"
				+ "s_single_company sc,s_product p,s_insurer i "
				+ "WHERE (it.FOREIGN_ENTERPRISE_ID = fe.FOREIGN_ENTERPRISE_ID(+)) AND "
				+ "(it.SINGLE_COMPANY_ID = sc.SINGLE_COMPANY_ID(+)) AND "
				+ "(it.PRODUCT_ID = p.PRODUCT_ID(+)) AND "
				+ "(it.INSURER_ID = i.INSURER_ID(+)) AND (it.is_valid is null or it.is_valid='1')";

		if (user.isForeignEnterpriseUser()) {
			sql += " and it.FOREIGN_ENTERPRISE_ID="
					+ user.getForeignEnterpriseId();
		} else if (user.isIndependentCompanyUser()) {
			sql += " and it.SINGLE_COMPANY_ID="
					+ user.getSingleCompanyId();
		} else {
			sql += " and (1=2)";
		}

		return sql;
	}

	public String M_apply_form_dropdown_single_company(SqlDataset ds) {
		String sql = "SELECT s_single_company.COMPANY_CHINESE_NAME,"
				+ "s_single_company.SINGLE_COMPANY_ID "
				+ "FROM s_single_company s_single_company WHERE " + " 1=1 ";
		sql = CommUtil.getRoleSql(sql, "s_single_company");
		sql += CommUtil.setConditionSQL(ds);

		return sql;
	}
	
	public String M_apply_form_dataset_excess_query(SqlDataset ds){
		String sql = "SELECT v_medical_bill.EMPLOYEE_ID,v_medical_bill.BILL_CODE," +
				"v_medical_bill.BILL_DATE,s_employee.EMPLOYEE_NAME," +
				"s_single_company.COMPANY_SHORT_NAME,v_medical_bill.BILL_AMOUNT," +
				"s_product.PRODUCT_NAME,v_medical_bill.VERIFY_AMOUNT,v_medical_bill.DISEASE_REASON " +
				"FROM v_medical_bill,s_employee s_employee,s_single_company s_single_company," +
				"s_send_list s_send_list,s_product s_product,[table] " +
				"WHERE (v_medical_bill.EMPLOYEE_ID = s_employee.EMPLOYEE_ID(+)) AND " +
				"(v_medical_bill.SINGLE_COMPANY_ID = s_single_company.SINGLE_COMPANY_ID(+)) AND " +
				"(v_medical_bill.SENDLIST_ID = s_send_list.SEND_LIST_ID(+)) AND " +
				"(v_medical_bill.PRODUCT_ID = s_product.PRODUCT_ID(+)) and [field] " ;
		Date d1=ds.parameters().getDate("date1");
		Date d2=ds.parameters().getDate("date2");
		//new SimpleDateFormat("yyyy-MM-dd").format(d1);
		String date1=new SimpleDateFormat("yyyy-MM-dd").format(d1);
			String date2=new SimpleDateFormat("yyyy-MM-dd").format(d2);
				if(!date1.equals("")&&!date2.equals("")){
					sql+="AND (s_send_list.send_date <= to_date('"+date1+"','yyyy-MM-dd') and s_send_list.send_date >= to_date('"+date2+"','yyyy-MM-dd')) ";
				}
				sql+=" and s_send_list.employee_id in (select f.employee_id from s_send_list f";
				if(!date1.equals("")&&!date2.equals("")){
					sql+=" where f.send_date <= to_date('"+date1+"','yyyy-MM-dd') and  f.send_date >= to_date('"+date2+"','yyyy-MM-dd')) ";
				}
				sql+=" group by  f.employee_id ";
				int amount = 0;
				try {
					amount = ds.parameters().getInt("amount");
				} catch (Exception e) {
					 amount = 0;
				}
				if(amount>0){
					sql+="having sum(f.amount)>="+amount+")";
				}

	sql = CommUtil.getRoleSql(sql, "s_single_company");


	return sql;
	}
	public String M_apply_form_dataset_single_company_apply(SqlDataset ds) {
		String sql = "SELECT v_medical_bill.EMPLOYEE_ID,s_employee.EMPLOYEE_CODE," +
				"s_employee.EMPLOYEE_NAME,v_medical_bill.VERIFY_DATE," +
				"v_medical_bill.VERIFY_AMOUNT,v_medical_bill.SINGLE_COMPANY_ID " +
				"FROM v_medical_bill,s_single_company s_single_company,s_employee s_employee " +
				"WHERE " +
				"(v_medical_bill.SINGLE_COMPANY_ID = s_single_company.SINGLE_COMPANY_ID) AND " +
				"(v_medical_bill.EMPLOYEE_ID = s_employee.EMPLOYEE_ID) AND " +
				"((v_medical_bill.IF_SENDED = '2'))";
		//sql = CommUtil.getRoleSql(sql, "s_single_company");
		sql += CommUtil.setConditionSQL(ds);

		return sql;
	}
	public String M_apply_form_dataset_employee_apply_query(SqlDataset ds) {
		String sql = "SELECT * " +
				"FROM " +
				"s_apply_form,s_medical_bill s_medical_bill," +
				"s_verify_result s_verify_result,s_send_list s_send_list,s_product " +
				"WHERE " +
				"(s_apply_form.APPLY_FORM_ID = s_medical_bill.APPLY_FORM_ID(+)) AND " +
				"(s_medical_bill.MEDICAL_BILL_ID = s_verify_result.MEDICAL_BILL_ID) AND " +
				"(s_verify_result.SENDLIST_ID = s_send_list.SEND_LIST_ID) AND" +
				"(s_medical_bill.product_id=s_product.product_id)";
	//	sql = CommUtil.getRoleSql(sql, "s_single_company");
		sql += CommUtil.setConditionSQL(ds);
		sql+=" ORDER BY s_medical_bill.BILL_CODE";

		return sql;
	}
}
